package cn.hd01.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import cn.hd01.repository.entity.COrderPayment;

@Repository
public interface COrderPaymentRepository extends JpaRepository<COrderPayment, Integer> {
	Page<COrderPayment> findAll(Specification<COrderPayment> spec, Pageable pageable);
	
	List<COrderPayment> findByOrderIdOrderByPayTimeDesc(Integer OrderId);
	
	@Query(value="select sum(amount), sum(fee_amount), SUM(IF(is_balance = 0, distributor_rebate, 0)), SUM(IF(is_balance = 1, distributor_rebate, 0)) from c_order_payment where order_id = ?1", nativeQuery=true)
	List<Object[]> findAllAmount(Integer orderId);
	
	@Query(value="SELECT IFNULL(SUM(IF(is_balance = 0,IFNULL(distributor_rebate,0)+IFNULL(recomm_distributor_rebate,0),0)),0) AS down_rebate,IFNULL(SUM(IF(is_balance = 1,IFNULL(distributor_rebate,0)+IFNULL(recomm_distributor_rebate,0),0)),0) undown_rebate FROM c_order_payment WHERE DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')", nativeQuery=true)
	List<Object[]> findRebate();
	
	@Modifying
	@Transactional
	@Query(value = "delete from c_order_payment where order_id = ?1", nativeQuery = true)
	int deleteByOrderId(Integer orderId);
	
}
